package com.infore.exchange.main.dao;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface StatisticalMapper {

/*    @Select("select '大气环境' as type,  DATE_FORMAT(receive_time,'%Y-%m-%d') as dateStr ,count(1) as  rowCount from db_envdatacenter.t_bas_air_message_record\n" +
            "where receive_time> DATE_SUB(CURDATE(), INTERVAL '${days}' DAY)\n" +
            "group by DATE_FORMAT(receive_time,'%Y-%m-%d')\n" +
            "union all\n" +
            "select '水环境' as type,  DATE_FORMAT(receive_time,'%Y-%m-%d') as dateStr ,count(1) as  rowCount from db_envdatacenter.t_bas_water_message_record\n" +
            "where receive_time> DATE_SUB(CURDATE(), INTERVAL '${days}' DAY)\n" +
            "group by DATE_FORMAT(receive_time,'%Y-%m-%d')\n" +
            "union all\n" +
            "select '污染源' as type,  DATE_FORMAT(receive_time,'%Y-%m-%d') as dateStr ,count(1) as  rowCount from db_envdatacenter.t_bas_sop_message_record\n" +
            "where receive_time> DATE_SUB(CURDATE(), INTERVAL '${days}' DAY)\n" +
            "group by DATE_FORMAT(receive_time,'%Y-%m-%d')\n" +
            "order by  dateStr")*/

    @Select("SELECT type as \"type\",dateStr as \"dateStr\",rowCount as \"rowCount\"  FROM db_envdatacenter.t_bas_day_resource_statistics WHERE  " +
            "dateStr>= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL '${days}' DAY),'%Y-%m-%d')")
    List<Map<String,Object>> queryDataCollectionByDays(@Param("days") Integer days);


//    mysql
/*    @Select("select  '大气环境' as \"type\" ,table_rows as  \"rowCount\" from information_schema.tables where table_schema='db_envdatacenter' and TABLE_NAME='t_bas_original_data_air'\n" +
            "union all\n" +
            "select  '水环境' as \"type\" ,table_rows as  \"rowCount\" from information_schema.tables where table_schema='db_envdatacenter' and TABLE_NAME='t_mid_hour_data_water'\n" +
            "union all\n" +
            "select  '污染源' as \"type\" ,sum(table_rows) as  \"rowCount\" from information_schema.tables where table_schema='db_envdatacenter' \n" +
            "and TABLE_NAME in ('t_bas_original_data_sop_water','t_bas_original_data_sop_air')")*/
//   达梦
/*    @Select("select '大气环境' as \"type\", TABLE_ROWCOUNT(OWNER, TABLE_NAME) as \"rowCount\"\n" +
            "from all_tables\n" +
            "where owner = 'db_envdatacenter'\n" +
            "  and TABLE_NAME = 't_bas_original_data_air'\n" +
            "union all\n" +
            "select '水环境' as \"type\", TABLE_ROWCOUNT(OWNER, TABLE_NAME) as \"rowCount\"\n" +
            "from all_tables\n" +
            "where owner = 'db_envdatacenter'\n" +
            "  and TABLE_NAME = 't_mid_hour_data_water'\n" +
            "union all\n" +
            "select '污染源' as \"type\", sum(TABLE_ROWCOUNT(OWNER, TABLE_NAME)) as \"rowCount\"\n" +
            "from all_tables\n" +
            "where owner = 'db_envdatacenter'\n" +
            "  and TABLE_NAME in ('t_bas_original_data_sop_water', 't_bas_original_data_sop_air')")*/
    List<Map<String,Object>> queryDataCollectionRatio(@Param("driverAlias")String driverAlias);


    @Insert("insert into db_envdatacenter.t_bas_day_resource_statistics \n" +
            "select '大气环境' as type,  DATE_FORMAT(receive_time,'%Y-%m-%d') as dateStr ,count(1) as  rowCount from db_envdatacenter.t_bas_air_message_record\n" +
            "where receive_time> DATE_SUB(CURDATE(), INTERVAL '1' DAY)\n" +
            "group by DATE_FORMAT(receive_time,'%Y-%m-%d')\n" +
            "union all\n" +
            "select '水环境' as type,  DATE_FORMAT(receive_time,'%Y-%m-%d') as dateStr ,count(1) as  rowCount from db_envdatacenter.t_bas_water_message_record\n" +
            "where receive_time> DATE_SUB(CURDATE(), INTERVAL '1' DAY)\n" +
            "group by DATE_FORMAT(receive_time,'%Y-%m-%d')\n" +
            "union all\n" +
            "select '污染源' as type,  DATE_FORMAT(receive_time,'%Y-%m-%d') as dateStr ,count(1) as  rowCount from db_envdatacenter.t_bas_sop_message_record\n" +
            "where receive_time> DATE_SUB(CURDATE(), INTERVAL '1' DAY)\n" +
            "group by DATE_FORMAT(receive_time,'%Y-%m-%d')")
    int DayResourceStatistics();

    @Delete("DELETE  FROM db_envdatacenter.t_bas_day_resource_statistics " +
            "WHERE  dateStr>= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL '1' DAY),'%Y-%m-%d')")
    int DayResourceDelete();

}
